﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data;

namespace EasyChat_Server.DAL
{
   public  class DBHelper
    {

      
       #region 通用方法
        // 数据连接池
      private OleDbConnection con;
       // private static OleDbConnection myConnection;
       /// <summary>
       /// 连接字符串
       /// </summary>
       /// <param name="udl"></param>
        private static string GetOleConnection(string udl)
       { 
            try
            {
                string str = System.AppDomain.CurrentDomain.BaseDirectory;
                string udlName = udl+".udl";
                return "File Name =" + str + "\\DBUDL\\" + udlName;
            }
            catch (Exception)
            {                
                throw;
            }
       }
    
       #endregion

       #region 执行sql字符串
       /// <summary>
       /// 执行不带参数的SQL语句
       /// </summary>
       /// <param name="Sqlstr"></param>
       /// <returns></returns>
       //public static int ExecuteSql(String Sqlstr,string udl)
       //{
       //    open(udl);
       //  OleDbCommand cmd= myConnection.CreateCommand();
       //  cmd.CommandText = Sqlstr;//准备做什么
       
       //    int i = cmd.ExecuteNonQuery();//对象是对insert,update,delete语句返回受影响的行数
       //    myConnection.Close();
       //    return i;
       //}

       /// <summary>  
       /// 执行带参数的SQL语句  
       /// </summary>  
       /// <param name="Sqlstr">SQL语句</param>  
       /// <param name="param">参数对象数组</param>  
       /// <returns></returns>  
       public static int ExecuteSql(String cmdText,OleDbParameter[]param, string udl)
       {
           String ConnStr = GetOleConnection(udl);
           using (OleDbConnection conn = new OleDbConnection(ConnStr))
           {
               OleDbCommand cmd = new OleDbCommand();
               cmd.Connection = conn;
               cmd.CommandText = cmdText;
               cmd.Parameters.AddRange(param);
               conn.Open();
               int i = cmd.ExecuteNonQuery();
               conn.Close();
               return i;
           }
       }



       #endregion

       #region 操作存储过程
  

       /// <summary>
       /// 创建Command对象用于访问存储过程
       /// </summary>
       /// <param name="procName">存储过程的名字</param>
       /// <param name="prams">存储过程的输入参数列表</param>
       /// <returns>Command对象</returns>
       private OleDbCommand CreateCommand(string procName, OleDbParameter[] prams, string udl)
       {
           // 确定连接是打开的
           Open(udl);
           //command = new SqlCommand( sprocName, new SqlConnection( ConfigManager.DALConnectionString ) );
           OleDbCommand cmd = new OleDbCommand(procName, con);
           cmd.CommandType = CommandType.StoredProcedure;
           // 添加存储过程的输入参数列表
           if (prams != null)
           {
               foreach (OleDbParameter parameter in prams)
                   cmd.Parameters.Add(parameter);
           }
           // 返回Command对象
           return cmd;
       }
 
       /// <summary>
       /// 运行存储过程(已重载)
       /// </summary>
       /// <param name="procName">存储过程的名字</param>
       /// <param name="prams">存储过程的输入参数列表</param>
       /// <returns>存储过程的返回值</returns>
       public void RunProc(string procName, OleDbParameter[] prams,string udl)
       {
           try
           {
               OleDbCommand cmd = CreateCommand(procName, prams, udl);
               cmd.ExecuteNonQuery();
               this.Close();
           }
           catch (Exception)
           {
               
               throw;
           }
           
       }
       #endregion

       #region 数据库连接和关闭
       /// <summary>
       /// 打开连接池
       /// </summary>
       private void Open(string udl)
       {
           // 打开连接池
           if (con == null)
           {
               //这里不仅需要using System.Configuration;还要在引用目录里添加
               con = new OleDbConnection(GetOleConnection(udl));
               con.Open();
           }
       }
       /// <summary>
       /// 关闭连接池
       /// </summary>
       public void Close()
       {
           if (con != null)
               con.Close();
       }
       /// <summary>
       /// 释放连接池
       /// </summary>
       public void Dispose()
       {
           // 确定连接已关闭
           if (con != null)
           {
               con.Dispose();
               con = null;
           }
       }
       #endregion
    }
}
